package com.yunkeji.dao.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yunkeji.common.core.mapper.BaseMapperPlus;
import com.yunkeji.dao.domain.CallSheetInfo;
import com.yunkeji.dao.domain.PayDetail;
import com.yunkeji.dao.vo.MonthStatisticsVo;
import com.yunkeji.dao.vo.PayDetailVo;
import com.yunkeji.dao.vo.StatisticsVo;
import com.yunkeji.dao.vo.broker.InviteInfoVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 充值明细Mapper接口
 *
 * @author 老张
 * @date 2024-05-27
 */
public interface PayDetailMapper extends BaseMapperPlus<PayDetailMapper, PayDetail, PayDetailVo> {

    @Select("<script>" +
        "SELECT  " +
        "c.*,  " +
        "u.sex as sex " +
        "FROM  " +
        "ykj_pay_detail c  " +
        "LEFT JOIN ykj_app_user u ON c.user_id = u.id " +
        "where 1=1 " +
        "<if test='platformId!=null'>" +
        "and c.platform_id = #{platformId} " +
        "</if>" +
        "<if test='payType!=null'>" +
        "and c.pay_type = #{payType} " +
        "</if>" +
        "<if test='orderId!=null'>" +
        "and c.order_id = #{orderId} " +
        "</if>" +
        "<if test='thirdId!=null'>" +
        "and c.third_id = #{thirdId} " +
        "</if>" +
        "<if test='rechargeType!=null'>" +
        "and c.recharge_type = #{rechargeType} " +
        "</if>" +
        "<if test='status!=null'>" +
        "and c.status = #{status} " +
        "</if>" +
        "<if test='payTimeStart!=null'>" +
        "and c.create_time <![CDATA[>=]]> #{payTimeStart} " +
        "</if>" +
        "<if test='payTimeEnd!=null'>" +
        "and c.create_time <![CDATA[<=]]> #{payTimeEnd} " +
        "</if>" +
        "order by c.create_time desc " +
        "</script>")
    Page<PayDetailVo> selectListPage(@Param("platformId") Long platformId,
                                         @Param("payType") Integer payType,
                                         @Param("orderId") String orderId,
                                         @Param("thirdId") String thirdId,
                                         @Param("rechargeType") Integer rechargeType,
                                         @Param("status") Integer status,
                                         @Param("payTimeStart") String payTimeStart,
                                         @Param("payTimeEnd") String payTimeEnd,
                                         @Param("Page") Page<CallSheetInfo> Page);


    @Select("<script>" +
        "SELECT " +
        " t1.days days, " +
        " t1.branch branch, " +
        " t1.regCount reg_count, " +
        " ifnull( t2.sum_price, 0 ) sum_price, " +
        " ifnull( t2.sum_count, 0 ) sum_count, " +
        " ifnull( t3.cny_price, 0 ) first_price, " +
        " ifnull( t3.counts, 0 ) first_count, " +
        " ifnull( t4.sum_price, 0 ) reg_day_price  " +
        "FROM " +
        " ( SELECT date( create_time ) days, branch, count( 1 ) regCount " +
        " FROM ykj_app_user " +
        "<if test='isInvite!=0'>" +
        " where pid <![CDATA[>]]> 0 " +
        "</if>" +
        "<if test='isInvite!=1'>" +
        " where pid is null " +
        "</if>" +
        " GROUP BY date( create_time ), branch ) t1 " +
        " LEFT JOIN ( " +
        " SELECT " +
        "  sum( cny_price ) sum_price, " +
        "  count( 1 ) sum_count, " +
        "  branch, " +
        "  date( create_time ) days  " +
        " FROM " +
        "  ykj_pay_detail  " +
        " WHERE " +
        "  `status` = 1  " +
        " GROUP BY " +
        "  branch, " +
        "  date( create_time )  " +
        " ) t2 ON t1.days = t2.days  " +
        " AND t1.branch = t2.branch " +
        " LEFT JOIN ( " +
        " SELECT " +
        "  count( 1 ) counts, " +
        "  sum( c1.prices ) cny_price, " +
        "  c1.branch, " +
        "  c1.days  " +
        " FROM " +
        "  ( " +
        "  SELECT " +
        "   count( 1 ) counts, " +
        "   sum( de.cny_price ) prices, " +
        "   ypd.branch, " +
        "   date( ypd.days ) days  " +
        "  FROM " +
        "   ykj_pay_detail de " +
        //查询的是每日的首充用户，所以会出现有些数据有充值数据，但没有首充次数和金额的情况
        "   LEFT JOIN ( SELECT user_id, branch, min( create_time ) days FROM ykj_pay_detail WHERE `status` = 1 GROUP BY user_id, branch ORDER BY days ) ypd ON de.user_id = ypd.user_id  " +
        "   AND de.branch = ypd.branch  " +
        "   AND de.create_time = ypd.days  " +
        "  WHERE " +
        "   de.`status` = 1  " +
        "  GROUP BY " +
        "   ypd.branch, " +
        "   days  " +
        "  ) c1  " +
        " GROUP BY " +
        "  c1.days, " +
        "  c1.branch  " +
        " ) t3 ON t1.days = t3.days  " +
        " AND t1.branch = t3.branch " +
        " LEFT JOIN ( " +
        " SELECT " +
        "  c1.branch, " +
        "  c1.days, " +
        "  sum( c1.cny_price ) sum_price  " +
        " FROM " +
        "  ( " +
        "  SELECT " +
        "   u.id, " +
        "   d.cny_price, " +
        "   d.branch, " +
        "   date( u.create_time ) days  " +
        "  FROM " +
        "   ykj_app_user u " +
        "   LEFT JOIN ykj_pay_detail d ON date( u.create_time ) = date( d.create_time )  " +
        "   AND u.id = d.user_id  " +
        "   AND u.branch = d.branch  " +
        "  WHERE " +
        "   d.`status` = 1  " +
        "  ) c1  " +
        " GROUP BY " +
        "  c1.days, " +
        "  c1.branch  " +
        " ) t4 ON t1.days = t4.days  " +
        " AND t1.branch = t4.branch where 1 = 1" +
        "<if test='date!=null'>" +
        "and t1.days = #{date} " +
        "</if>" +
        "<if test='branch!=null'>" +
        "and t1.branch = #{branch} " +
        "</if>" +
        "</script>")
    Page<StatisticsVo> queryStatistics(@Param("date") String date,
                                       @Param("branch") Integer branch,
                                       @Param("isInvite") Integer isInvite,
                                       @Param("Page") Page<CallSheetInfo> Page);


    @Select("<script>" +
        "SELECT  " +
        " t1.days days,  " +
        " t1.regCount reg_count,  " +
        " ifnull( t2.sum_price, 0 ) sum_price,  " +
        " ifnull( t2.sum_count, 0 ) sum_count,  " +
        " ifnull( t3.cny_price, 0 ) first_price,  " +
        " ifnull( t3.counts, 0 ) first_count,  " +
        " ifnull( t4.sum_price, 0 ) reg_day_price   " +
        "FROM  " +
        " ( SELECT date( create_time ) days, count( 1 ) regCount FROM ykj_app_user where branch = #{branch} GROUP BY days ) t1  " +
        " LEFT JOIN (  " +
        " SELECT  " +
        "  sum( cny_price ) sum_price,  " +
        "  count( 1 ) sum_count,  " +
        "  date( create_time ) days   " +
        " FROM  " +
        "  ykj_pay_detail   " +
        " WHERE  " +
        "  `status` = 1 and branch = #{branch} " +
        " GROUP BY  " +
        "  days  " +
        " ) t2 ON t1.days = t2.days   " +
        " LEFT JOIN (  " +
        " SELECT  " +
        "  count( 1 ) counts,  " +
        "  sum( c1.prices ) cny_price,  " +
        "  c1.days   " +
        " FROM  " +
        "  (  " +
        "  SELECT  " +
        "   count( 1 ) counts,  " +
        "   sum( de.cny_price ) prices,  " +
        "   date( ypd.days ) days   " +
        "  FROM  " +
        "   ykj_pay_detail de " +
        "   LEFT JOIN ( SELECT user_id, min( create_time ) days FROM ykj_pay_detail WHERE `status` = 1 and branch = #{branch} GROUP BY user_id ORDER BY days ) ypd ON de.user_id = ypd.user_id   " +
        "   AND de.create_time = ypd.days   " +
        "  WHERE  " +
        "   de.`status` = 1 and de.branch = #{branch}  " +
        "  GROUP BY  " +
        "   days   " +
        "  ) c1   " +
        " GROUP BY  " +
        "  c1.days  " +
        " ) t3 ON t1.days = t3.days   " +
        " LEFT JOIN (  " +
        " SELECT  " +
        "  c1.days,  " +
        "  sum( c1.cny_price ) sum_price   " +
        " FROM  " +
        "  (  " +
        "  SELECT  " +
        "   u.id,  " +
        "   d.cny_price,  " +
        "   date( u.create_time ) days   " +
        "  FROM  " +
        "   ykj_app_user u  " +
        "   LEFT JOIN ykj_pay_detail d ON date( u.create_time ) = date( d.create_time )   " +
        "   AND u.id = d.user_id   " +
        "  WHERE  " +
        "   d.`status` = 1  and u.branch = #{branch} and d.branch = #{branch} " +
        "  ) c1   " +
        " GROUP BY  " +
        "  c1.days  " +
        " ) t4 ON t1.days = t4.days   " +
        " where date_format(t1.days, '%Y-%m') = #{date}  order by t1.days desc" +
        "</script>")
    Page<StatisticsVo> queryInviteStatistics(@Param("date") String date,
                                       @Param("branch") Integer branch,
                                       @Param("Page") Page<CallSheetInfo> Page);

    @Select("<script>" +
        "SELECT " +
        " d.branch, " +
        " sum(d.cny_price) sum_price " +
        "FROM " +
        " ykj_pay_detail d left join ykj_app_user u on d.user_id = u.id " +
        "WHERE " +
        " d.`status` = 1 " +
        "<if test='startDate!=null'>" +
        "and date(d.create_time) <![CDATA[>=]]> #{startDate} " +
        "</if>" +
        "<if test='endDate!=null'>" +
        "and date(d.create_time) <![CDATA[<=]]> #{endDate} " +
        "</if>" +
        "<if test='branch!=null'>" +
        "and d.branch = #{branch} " +
        "</if>" +
        "<if test='isInvite!=0'>" +
        " and u.pid <![CDATA[>]]> 0 " +
        "</if>" +
        "<if test='isInvite!=1'>" +
        " and u.pid is null " +
        "</if>" +
        "GROUP BY d.branch having d.branch > 0" +
        "</script>")
    Page<MonthStatisticsVo> queryMonthStatistics(@Param("startDate") String startDate,
                                                 @Param("endDate") String endDate,
                                                 @Param("branch") Integer branch,
                                                 @Param("isInvite") Integer isInvite,
                                                 @Param("Page") Page<CallSheetInfo> Page);

    @Select("<script>" +
        "SELECT " +
        " u.id, " +
        " date( u.create_time ) register_time, " +
        " u.platform_id, " +
        " e.head_url, " +
        " e.nick_name, " +
        " u.sex, " +
        " ifnull( sum( d.cny_price ), 0 ) recharge_amount, " +
        " ifnull( ( t1.duration + t2.duration ) / 60, 0 ) AS call_minutes, " +
        " ifnull( sum( i_2.earn_cost ), 0 ) all_cost, " +
        " ifnull( t3.price, 0 ) gift_cost  " +
        "FROM " +
        " ykj_app_user u " +
        " LEFT JOIN ykj_app_user_ext e ON u.id = e.id " +
        " LEFT JOIN ykj_pay_detail d ON ( u.id = d.user_id AND d.STATUS = 1 AND d.pay_type <![CDATA[<]]> 4 ) " +
        " LEFT JOIN ( SELECT sum( duration ) AS duration, caller_id FROM ykj_call_sheet_info GROUP BY caller_id ) t1 ON t1.caller_id = u.id " +
        " LEFT JOIN ( SELECT sum( duration ) AS duration, answer_id FROM ykj_call_sheet_info GROUP BY answer_id ) t2 ON t2.answer_id = u.id " +
        " LEFT JOIN ykj_call_sheet_info i_2 ON ( u.id = i_2.benefit_uid ) " +
        " LEFT JOIN ( SELECT sum( price ) AS price, rec_uid FROM ykj_gift_receive GROUP BY rec_uid ) t3 ON t3.rec_uid = u.id  " +
        "WHERE " +
        " u.branch = #{branch} and date_format(u.create_time, '%Y-%m') = #{date} " +
        "<if test='sex!=null'>" +
        "and u.sex = #{sex} " +
        "</if>" +
        "<if test='platformId!=null'>" +
        "and u.platform_id = #{platformId} " +
        "</if>" +
        "GROUP BY " +
        " u.id  " +
        "ORDER BY " +
        " u.create_time DESC" +
        "</script>")
    Page<InviteInfoVo> queryInviteInfo(@Param("date") String date,
                                       @Param("branch") Integer branch,
                                       @Param("sex") Integer sex,
                                       @Param("platformId") Long platformId,
                                       @Param("Page") Page<CallSheetInfo> Page);

}
